﻿Public Class Quickbooks

    '1  SKU
    '2  ATTRIBUTE
    '3

    Private qbTable As DataTable

    Private quickBooksDataArray(250) As String
    Private openFileDialog1 As New OpenFileDialog()
    Private lineArray() As String
    Private tempArray() As String
    Private J As Integer = 0
    Private K As Integer = 0
    Public isLoaded As Boolean = False


    Public Sub New()
    End Sub

    Public Sub New(ByVal doLoad As Boolean)
        If doLoad Then Me.LoadFile()
    End Sub

    Public Function GetData() As Array
        Return quickBooksDataArray
    End Function

    Public Function getDataTable() As DataTable

        Dim invItemCnt As Integer = 0

        Dim currentLineInArray As Integer
        Dim loadedFileArray() As String
        Dim tempSplitArray() As String
        Dim fullTextCurrentLine As String

        openFileDialog1.Filter = "Quickbooks|*.iif"
        openFileDialog1.Title = "Open QuickBooks Exported File..."
        openFileDialog1.InitialDirectory = "c:\magento"

        Try

            If openFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                Dim myStream As System.IO.StreamReader = New System.IO.StreamReader(openFileDialog1.FileName)

                loadedFileArray = myStream.ReadToEnd.Split(CChar(vbCr))
                myStream.Close()

                Do While currentLineInArray < loadedFileArray.Count()
                    'fullTextCurrentLine = loadedFileArray(currentLineInArray)

                    tempSplitArray = loadedFileArray(currentLineInArray).Split(CChar(vbTab))
                    tempSplitArray(0) = tempSplitArray(0).Remove(0, 1)



                    If tempSplitArray(0).Contains("!INVITEM") And invItemCnt = 0 Then
                        invItemCnt = +1

                        'tempSplitArray = loadedFileArray(currentLineInArray).Split(CChar(vbTab))

                        qbTable = New DataTable("Quickbooks")

                        Dim x As Integer = 0 'skip invitem bollocks
                        Do While x < tempSplitArray.Count
                            If tempSplitArray(x).Equals("QNTY") Then
                                'for some reason quickbooks has to QNTY columns named identically.. fooken dooshes!
                                If invItemCnt = 2 Then
                                    tempSplitArray(x) = "QNTY_OTHER"
                                    invItemCnt += 1
                                End If
                                If invItemCnt = 1 Then
                                    tempSplitArray(x) = "QNTY_IN_STOCK"
                                    invItemCnt += 1
                                End If
                               
                            End If
                            Dim newColumn As DataColumn = New DataColumn(tempSplitArray(x).ToString)
                            newColumn.DataType = System.Type.GetType("System.String")
                            qbTable.Columns.Add(newColumn)
                            x += 1
                        Loop
                    End If

                    'add all the items data 
                    If tempSplitArray(0).Equals("INVITEM") Then

                        Dim newRow As DataRow
                        newRow = qbTable.NewRow

                        tempSplitArray = loadedFileArray(currentLineInArray).Split(CChar(vbTab))
                        Dim x As Integer = 0 'set as 1 to skip invitem bollocks
                        Try
                            Do While x < tempSplitArray.Count

                                newRow(x) = tempSplitArray(x).ToString
                                x += 1
                            Loop
                        Catch ex As Exception
                            MessageBox.Show("TMParray has itesm-" & tempSplitArray.Count & "  " & "Table has columne- " & newRow.Table.Columns.Count.ToString & " x is at - " & x.ToString & vbCr & ex.ToString)
                        End Try
                       
                        qbTable.Rows.Add(newRow)
                    End If

                    currentLineInArray += 1
                Loop
            End If


        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

        isLoaded = True

        Return qbTable
    End Function




    Public Sub quickLoad(ByVal QBfileName As String)

        Try

            If My.Computer.FileSystem.FileExists(QBfileName) Then
                openFileDialog1.FileName = QBfileName
                Dim myStream As System.IO.StreamReader = New System.IO.StreamReader(openFileDialog1.FileName)

                lineArray = myStream.ReadToEnd.Split(CChar(vbCr))
                myStream.Close()

                Do While J < lineArray.Count()
                    tempArray = lineArray(J).Split(CChar(vbTab))

                    If quickBooksDataArray.Length = K Then 'NONDESTRUCTIVE INCREASE SIZE OF ARRAY TO ACCOMODATE MORE ITEMS IF NECESSARY
                        ReDim Preserve quickBooksDataArray(quickBooksDataArray.Length + 250)

                    End If

                    If tempArray.Length >= 12 Then
                        If tempArray(0).ToString.Contains("INVITEM") And Not tempArray(0).ToString.Contains("!") And tempArray(5).Length > 0 And tempArray(10).Length > 0 And tempArray(12).Length > 0 And tempArray(13).Length > 0 Then
                            quickBooksDataArray(K) = GetSKU(tempArray(1)) & CChar(vbTab) & GetAttribute(tempArray(1)) & CChar(vbTab) & tempArray(6) & CChar(vbTab) & tempArray(10) & CChar(vbTab) & tempArray(12) & CChar(vbTab) & tempArray(13) & CChar(vbTab) & tempArray(28) & CChar(vbTab) & GetWebName(tempArray(21))

                            K += 1 'increment if a valid entry is found
                        End If
                    End If
                    J += 1
                Loop


                ''reset counters and trim array to correct size to avoid NULL exception
                J = 0
                K = 0

                Do While quickBooksDataArray(J) <> ""
                    J += 1
                Loop
                ReDim Preserve quickBooksDataArray(J - 1)
            End If


        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

        isLoaded = True

    End Sub


    Public Sub LoadFile()
        openFileDialog1.Filter = "Quickbooks|*.iif"
        openFileDialog1.Title = "Open QuickBooks Exported File..."
        openFileDialog1.InitialDirectory = "c:\magento"

        Try

            If openFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                Dim myStream As System.IO.StreamReader = New System.IO.StreamReader(openFileDialog1.FileName)

                lineArray = myStream.ReadToEnd.Split(CChar(vbCr))
                myStream.Close()

                Do While J < lineArray.Count()
                    tempArray = lineArray(J).Split(CChar(vbTab))

                    If quickBooksDataArray.Length = K Then 'NONDESTRUCTIVE INCREASE SIZE OF ARRAY TO ACCOMODATE MORE ITEMS IF NECESSARY
                        ReDim Preserve quickBooksDataArray(quickBooksDataArray.Length + 250)

                    End If

                    If tempArray.Length >= 12 Then
                        If tempArray(0).ToString.Contains("INVITEM") And Not tempArray(0).ToString.Contains("!") And tempArray(5).Length > 0 And tempArray(10).Length > 0 And tempArray(12).Length > 0 And tempArray(13).Length > 0 Then
                            quickBooksDataArray(K) = GetSKU(tempArray(1)) & CChar(vbTab) & GetAttribute(tempArray(1)) & CChar(vbTab) & tempArray(6) & CChar(vbTab) & tempArray(10) & CChar(vbTab) & tempArray(12) & CChar(vbTab) & tempArray(13) & CChar(vbTab) & tempArray(28) & CChar(vbTab) & GetWebName(tempArray(21))

                            K += 1 'increment if a valid entry is found
                        End If
                    End If
                    J += 1
                Loop


                ''reset counters and trim array to correct size to avoid NULL exception
                J = 0
                K = 0

                Do While quickBooksDataArray(J) <> ""
                    J += 1
                Loop
                ReDim Preserve quickBooksDataArray(J - 1)
            End If


        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

        isLoaded = True
    End Sub


    Public Function GetSKU(ByVal SKU As String) As String
        Dim rtn As String
        Dim temp() As String = SKU.Split(CChar(":"))

        If temp(temp.Count - 1).Equals("") Then
            rtn = "NoName"
        Else
            rtn = """" & (temp(temp.Count - 1)) & """"
        End If
        Return rtn
    End Function

    Public Function GetAttribute(ByVal category As String) As String
        Dim answer As String = ""
        Dim temp() As String = category.Split(CChar(":"))
        category = temp(0)

        Select Case category
            Case "ADD-ON CARD", "BLUETOOTH", "CABLES", "CLEANING", "FLASH DRIVE", "FLOPPY DRIVES"
                answer = "Peripherals"
            Case "GAME CONTROLLERS", "HEADSET", "HUBS", "INK", "KEYBOARD", "LAPTOPCASE", "DOCKING STATION"
                answer = "Peripherals"
            Case "MEDIA", "POWER ADAPTER", "SCANNER", "SOUND CARDS", "SPEAKERS", "MICROPHONE"
                answer = "Peripherals"
            Case "VIDEO CAPTURE", "WEBCAM", "TV TUNER CARD", "CARD READER", "KVM SWITCH", "LAPTOP CASE"
                answer = "Peripherals"
            Case "LAPTOPS", "LAPTOP CASE", "NETBOOK"
                answer = "Laptops"
            Case "CPU", "MICE", "MODEM"
                answer = "Processors"
            Case "ATX CASE"
                answer = "Cases"
            Case "ATX PSU"
                answer = "PSU"
            Case "CAMERAS"
                answer = "Cameras"
            Case "COOLING"
                answer = "Cooling"
            Case "DVD PLAYERS"
                answer = "Optical Drives"
            Case "ENCLOSURE", "HD"
                answer = "Hard Drives"
            Case "GRAPHICS CARD"
                answer = "Graphics Cards"
            Case "MONITOR"
                answer = "Monitors"
            Case "MOTHERBOARD"
                answer = "Motherboards"
            Case "OPTICAL DRIVE"
                answer = "Optical Drives"
            Case "PRINTER"
                answer = "Printers"
            Case "RAM"
                answer = "RAM"
            Case "SOFTWARE"
                answer = "Software"
            Case "NAS", "NETWORKING"
                answer = "Networking"
            Case "MODDING ACCESSORIES"
                answer = "Modding"
            Case "PC SYSTEM"
                answer = "Desktop"
            Case "BATTERY", "DVD PLAYERS", "LAPTOP SCREENS", "PAPER", "POWER SURGE", "RADIO", "TV STAND", "UPS", "LAPTOP KEYBOARDS"
                answer = "skipped"
            Case Else
                answer = "error"
        End Select

        Return answer
    End Function


    Public Function GetWebName(ByVal str As String) As String
        Dim tmp As String
        tmp = str.Replace(CChar(""""), CChar(" "))
        tmp = tmp.Replace(CChar(","), CChar(" "))
        tmp = tmp.Trim(CChar(" "))
        'tmp = """" & tmp & """"
        Return tmp
    End Function
End Class
